﻿using MXComponent.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MXComponent.DAL
{
    public partial class formuladetailhistoryDB
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public formuladetailhistoryDB()
        {
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">model</param>
        public void AddRecord(formuladetailhistoryData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into formuladetailhistory(");
            strSql.Append(@"formulaid,warehouseid,planqty,realqty,createtime,formulahistoryid,ordernum,materialid)");
            strSql.Append(" values (");
            strSql.Append(@"@formulaid,@warehouseid,@planqty,@realqty,@createtime,@formulahistoryid,@ordernum,@materialid)");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaid", SqlDbType.Int),
                    new SqlParameter("@warehouseid", SqlDbType.Int),
                    new SqlParameter("@planqty", SqlDbType.Int),
                    new SqlParameter("@realqty", SqlDbType.Int),
                    new SqlParameter("@createtime", SqlDbType.DateTime),
                    new SqlParameter("@formulahistoryid", SqlDbType.NVarChar,50),
                    new SqlParameter("@ordernum", SqlDbType.Int),
                    new SqlParameter("@materialid", SqlDbType.Int)
                };
            parameters[0].Value = model.formulaid;
            parameters[1].Value = model.warehouseid;
            parameters[2].Value = model.planqty;
            parameters[3].Value = model.realqty;
            parameters[4].Value = model.createtime == string.Empty ? null : model.createtime;
            parameters[5].Value = model.formulahistoryid;
            parameters[6].Value = model.ordernum;
            parameters[7].Value = model.materialid;
            object ret = SqlHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="realqty"></param>
        /// <param name="formulahistoryid"></param>
        /// <param name="ordernum"></param>
        public void UpdateRecord(formuladetailhistoryData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update  formuladetailhistory set realqty=@realqty ");
            strSql.Append(" where formulahistoryid = @formulahistoryid  and ordernum=@ordernum and warehouseid=@warehouseid ");
            SqlParameter[] parameters = {
             new SqlParameter("@realqty", SqlDbType.Int),
                    new SqlParameter("@formulahistoryid", SqlDbType.NVarChar,50),
                    new SqlParameter("@ordernum", SqlDbType.Int),
                    new SqlParameter("@warehouseid", SqlDbType.Int)
                };
            parameters[0].Value = model.realqty;
            parameters[1].Value = model.formulahistoryid;
            parameters[2].Value = model.ordernum;
            parameters[3].Value = model.warehouseid;

            SqlHelper.ExecteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="historyid">主键值</param>
        /// <returns>是否存在</returns>
        public bool Exists(formuladetailhistoryData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from formuladetailhistory");
            strSql.Append(" where formulahistoryid = @formulahistoryid  and ordernum=@ordernum and warehouseid=@warehouseid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulahistoryid", SqlDbType.NVarChar,50),
                    new SqlParameter("@ordernum", SqlDbType.Int),
                    new SqlParameter("@warehouseid", SqlDbType.Int)
                };
            parameters[0].Value = model.formulahistoryid;
            parameters[1].Value = model.ordernum;
            parameters[2].Value = model.warehouseid;

            return Convert.ToInt32(SqlHelper.ExecuteScalar( CommandType.Text, strSql.ToString(), parameters)) != 0;
        }

        public void AddRecordList(List<formuladetailhistoryData> fdhistoryList)
        {
            foreach (formuladetailhistoryData model in fdhistoryList)
            {
                if (Exists(model))
                {
                    UpdateRecord(model);
                }
                else
                {
                    AddRecord(model);
                }
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">限制条件</param>
        /// <returns>DataSet</returns>
        public DataSet GetMaterialReport(string starttime,string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"SELECT M.materialname,sum(realqty) totalnum
            FROM  dbo.formuladetailhistory H
            left join dbo.material M on H.materialid=M.materialid
            where H.createtime between '{0} 00:00:00' and '{1} 23:59:59' 
            group by M.materialname
            order by M.materialname", starttime, endtime);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public DataSet GetformulaChar(string starttime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"SELECT h.formulaid, f.formulaname,f.formuladesc, sum(h.plancount) plancount,sum(isnull(realcount,0))  realcount
            FROM  dbo.formulahistory 
            h left join dbo.formula f on h.formulaid=f.formulaid 
            where H.createtime between '{0} 00:00:00' and '{1} 23:59:59' 
            group by h.formulaid, f.formulaname,f.formuladesc
            order by  f.formulaname", starttime, endtime);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">限制条件</param>
        /// <returns>DataSet</returns>
        public DataSet GetformulaReport(string starttime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();

            //          strSql.AppendFormat(@" select f.formulaname, h.plancount,isnull((select top 1  count(1) from dbo.formuladetailhistory y where y.formulahistoryid=h.formulahistoryid and y.ordernum<>0   group by  warehouseid ),0)  realcount,h.createtime  
            //from dbo.formulahistory h left join dbo.formula f on h.formulaid=f.formulaid where h.createtime between '{0} 00:00:00' and '{1} 23:59:59' order by  h.createtime", starttime, endtime);
            strSql.AppendFormat(@" select h.formulahistoryid, f.formulaname, h.plancount,isnull(realcount,0)  realcount,h.createtime  
  from dbo.formulahistory h left join dbo.formula f on h.formulaid=f.formulaid where h.createtime between '{0} 00:00:00' and '{1} 23:59:59' order by  h.createtime", starttime, endtime);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        public DataSet Getformuladetailhistory(string starttime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat(@" select f.formulaname,w.warehousename,m.materialname, h.planqty,h.realqty,h.createtime,h.ordernum  from  dbo.formuladetailhistory h
  left join dbo.formula f on h.formulaid=f.formulaid
  left join dbo.materialwarehouse w on w.warehouseid=h.warehouseid
  left join dbo.material m on m.materialid=h.materialid where h.createtime between '{0} 00:00:00' and '{1} 23:59:59' and h.ordernum<>0 order by  h.createtime", starttime, endtime);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public DataSet Getformuladetailhistory(string formulahistoryid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat(@" select f.formulaname,w.warehousename,m.materialname, h.planqty,h.realqty,h.createtime,h.ordernum  from  dbo.formuladetailhistory h
  left join dbo.formula f on h.formulaid=f.formulaid
  left join dbo.materialwarehouse w on w.warehouseid=h.warehouseid
  left join dbo.material m on m.materialid=h.materialid where h.formulahistoryid='{0}'  and h.ordernum<>0 order by h.ordernum ,w.warehouseid", formulahistoryid);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
    }
}